Python query on Group by and Indexing

by: akitnava, 7 years ago


For the below table I wish to do the following:

A) Sort by Symbol and then by date

B) Subtract Av Price for current day - previous day and % variation

C) What if I want to calculate B only from a particular date eg: For apple, I wish to do the calculation only for 04/01/17 not 02/01/17

I could write the code only for B. Struggling with A and C. Any help will be useful. I am an amateur and will require guidance

code developed by me:
import pandas as pd
df4=pd.read_csv("Consolidated.csv",header=0,index_col=2)
del df4['newcolname']
del df4['dA']
del df4['dA1']
del df4['dA2']
df4 = df4.set_index("Symbol")
df4['Date'] = pd.to_datetime(df4['Date'])
df4.set_index(['Date', 'Series'], inplace=True)
df4.index = df4['Date']
del df4['Date']
df4.sort_index(inplace=True)
df4['dA'] = df4['Average Price'] - df4['Average Price'].shift(+1)
df4['dA1']= df4['Average Price'].shift(+1)
df4['dA2'] = df4['dA']/df4['dA1']*100
df4.to_csv("Consolidated.csv")  

Table: Dataframe: Consolidated.csv
Date      Symbol   Average Price

02/01/17  Apple    1004.35
03/01/17  Apple    2365.66
04/01/17  Apple    989.67
05/01/17  Apple    2358.03
11/10/17  Apple    2494.35
13/10/17  Banana   932.27
14/10/17  Banana   2529.48
15/10/17  Banana   924.62
16/10/17  Banana   929.7
13/10/17  Banana   2562.16


You must be logged in to post. Please login or register an account.



Hi please help me with a solution for the above question

-akitnava 7 years ago

You must be logged in to post. Please login or register an account.

A)  you can just use df.sort. So you can do something like df4.sort(['symbol','date']). .sort() also has an ascending arg, so you can specify asc or desc.

B) 

tempdf = df4[ (df4['symbol']) == 'AAPL' ]


Then do the average of the columns, now you have your value. For the temp df, to do it for all symbols, you could start by doing something like:

for sym in df4.symbols.unique():
    tempdf = df4[ (df4['symbol']) ==sym ]


C) Then use datetime to specify the range you're interested in. Example: https://stackoverflow.com/questions/29370057/select-dataframe-rows-between-two-dates

All of these questions are easily google-able, so, if my answers aren't enough, poke around.



-Harrison 7 years ago

You must be logged in to post. Please login or register an account.